import xlsxwriter
from io import BytesIO
from apis import api_routes
from sqlalchemy import text
from typing import List
from typing import Union
from urllib.parse import unquote, quote
from security.decoration import certification
from flask import g, request, jsonify, Response, abort
from public.local import local_time
from models.facility.facility import Facility
from datetime import datetime, timedelta
from models.owner.owner import Owner


@api_routes.route("/andon/facility", methods=["GET"])
@certification(auth=True, role=None)
def func_andon_facility_get_api():
    """
    查询设备总共ID，看前端是否需要， 完成API开发
    http://127.0.0.1:8080/api/andon/facility
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 设备报警
    """
    resp: list = []
    data: List[Facility] = g.session.query(Facility).order_by(Facility.id).all()
    for index, itr in enumerate(data):
        resp.append({
            "facility_id": itr.id,
            "name": itr.name,
        })

    return jsonify({'status': 200, 'data': resp})


@api_routes.route("/andon", methods=["GET"])
@certification(auth=True, role=None)
def func_andon_list_get_api():
    """
    设备报警记录查询, 只要有token 都可以访问, API 开发完成，对接PLC未完成 完成API开发
    组合方式, 一共4种，其他组合默认第一种
        /api/andon?index=1&size=10
        /api/andon?index=1&size=10&facility_id=1
        /api/andon?index=1&size=10&facility_id=1&start=2023-11-03&finish=2023-11-04
        /api/andon?index=1&size=10&start=2023-11-03&finish=2023-11-04
    http://127.0.0.1:8080/api/andon
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "index"         int      ****  第几页，默认为1
    "size"          int      ****  每页数据多少, 默认每页10条数据
    "facility_id"   int            设备ID
    "start"         string         开始日期
    "finish"        string         结束日期
    "owner_id"      Int            默认(下拉，键值对)，可以不选, 排班名称，1白班， 2晚班
    }
    :return:
        {'status': 400, 'data': data}
    ---
    tags:
      - 设备报警
    """
    index = request.args.get("index", default=1, type=int)
    size = request.args.get("size", default=10, type=int)
    ft_id: int = request.args.get("facility_id", default=None, type=int)
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)
    owner_id: int = request.args.get("owner_id", default=0, type=int)

    if owner_id not in [0, 1, 2]:
        abort(code=400)

    if start is not None and len(start) == 0:
        start = None
    if finish is not None and len(finish) == 0:
        finish = None

    if (owner_id and start is None) or (owner_id and finish is None) or (owner_id and start != finish):
        return jsonify({'status': 4001, 'msg': "选择班次不能跨天查询或未指定日期"})

    back_start = start
    back_finish = finish

    if start is not None:
        start = datetime.strptime(start, "%Y-%m-%d").strftime("%Y-%m-%d %H:%M:%S")
    if finish is not None:
        temp_date_time = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    db_owner: Union[Owner, None] = g.session.query(Owner).filter(Owner.id == owner_id).first()
    if db_owner is not None and db_owner.start <= db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        finish = "{} {}".format(back_finish, db_owner.finish.strftime("%H:%M:%S"))
    elif db_owner is not None and db_owner.start > db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        hour = db_owner.finish.hour
        temp_date_time = datetime.strptime(back_finish, "%Y-%m-%d") + timedelta(days=1) + timedelta(hours=hour)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    res: list = []
    sql_count = """
        SELECT 
            COUNT(andon.id)
        FROM andon INNER JOIN facility ON andon.facility_id = facility.id {}
    """
    sql_date = """
        SELECT 
            andon.kind, andon.message, coalesce(to_char(andon.time,'YYYY-MM-DD hh24:mi:ss'), null),
            andon.expenditure, andon.account, facility.workplace, facility.name
        FROM andon INNER JOIN facility ON andon.facility_id = facility.id {} ORDER BY andon.id LIMIT :arg1 OFFSET :arg2 
    """
    change: bool = False
    if ft_id is not None and start is None and finish is None:
        change = True
        sql_count = sql_count.format("WHERE facility.id = {}".format(ft_id))
        sql_date = sql_date.format("WHERE facility.id = {}".format(ft_id))
    elif ft_id is not None and start is not None and finish is not None:
        change = True
        temp: str = "WHERE facility.id = {} AND andon.time > '{}' AND andon.time < '{}'"
        sql_count = sql_count.format(temp .format(ft_id, start, finish))
        sql_date = sql_date.format(temp.format(ft_id, start, finish))
    elif ft_id is None and start is not None and finish is not None:
        change = True
        temp: str = "WHERE andon.time > '{}' AND andon.time < '{}'"
        sql_count = sql_count.format(temp.format(start, finish))
        sql_date = sql_date.format(temp.format(start, finish))
    if change is False:
        sql_count = sql_count.format("")
        sql_date = sql_date.format("")
    counts: int = g.session.execute(text(sql_count)).fetchone()[0]

    page: int = int((counts / size) + 1) if (counts / size) > int(counts / size) else int(counts / size)

    data = g.session.execute(text(sql_date), {"arg1": size, "arg2": ((index - 1) * size), "fid": ft_id}).fetchall()
    for itr in data:
        res.append({
            "kind": itr[0], "message": itr[1], "time": itr[2],
            "expenditure": itr[3], "account": itr[4], "workplace": itr[5], "facility_name": itr[6]
        })
    doc: dict = {
        "kind": "报警种类", "message": "报警信息", "time": "报警时间",
        "expenditure": "报警时间/秒", "account": "操作用户", "workplace": "设备位置", "facility_name": "设备名字"
    }
    return jsonify({'status': 200, 'data': res, "index": index, "size": size, "total": counts, "pages": page, "doc": doc})


@api_routes.route("/andon/output", methods=["GET"])
# @certification(auth=True, role=None)
def func_andon_list_output_get_api():
    """
    设备报警记录导出EXCEL API, 完成API开发
    组合方式, 一共4种，其他组合默认第一种
        /api/andon/output
        /api/andon/output?facility_id=1
        /api/andon/output?facility_id=1&start=2023-11-03&finish=2023-11-04
        /api/andon/output?start=2023-11-03&finish=2023-11-04
    http://127.0.0.1:8080/api/andon/output
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "file"          string         导出文件名，没有默认按照今天日期
    "facility_id"   int            设备ID
    "start"         string         开始日期
    "finish"        string         结束日期
    "owner_id"      Int            默认(下拉，键值对)，可以不选, 排班名称，1白班， 2晚班
    }
    :return:
        数据流
    ---
    tags:
      - 设备报警
    """
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)
    file: Union[str, None] = request.args.get("file", default=None, type=str)
    ft_id: int = request.args.get("facility_id", default=None, type=int)
    owner_id: int = request.args.get("owner_id", default=0, type=int)

    if start is not None and len(start) == 0:
        start = None
    if finish is not None and len(finish) == 0:
        finish = None

    if (owner_id and start is None) or (owner_id and finish is None) or (owner_id and start != finish):
        return Response()

    back_start = start
    back_finish = finish

    if start is not None:
        start = datetime.strptime(start, "%Y-%m-%d").strftime("%Y-%m-%d %H:%M:%S")
    if finish is not None:
        temp_date_time = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    db_owner: Union[Owner, None] = g.session.query(Owner).filter(Owner.id == owner_id).first()
    if db_owner is not None and db_owner.start <= db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        finish = "{} {}".format(back_finish, db_owner.finish.strftime("%H:%M:%S"))
    elif db_owner is not None and db_owner.start > db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        hour = db_owner.finish.hour
        temp_date_time = datetime.strptime(back_finish, "%Y-%m-%d") + timedelta(days=1) + timedelta(hours=hour)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    sql_date = """
        SELECT 
            andon.kind, andon.message, coalesce(to_char(andon.time,'YYYY-MM-DD hh24:mi:ss'), null),
            andon.expenditure, andon.account, facility.workplace, facility.name
        FROM andon INNER JOIN facility ON andon.facility_id = facility.id {} ORDER BY andon.id 
    """
    change: bool = False
    if ft_id is not None and start is None and finish is None:
        change = True
        sql_date = sql_date.format("WHERE facility.id = {}".format(ft_id))
    elif ft_id is not None and start is not None and finish is not None:
        change = True
        temp: str = "WHERE facility.id = {} AND andon.time > '{}' AND andon.time < '{}'"
        sql_date = sql_date.format(temp.format(ft_id, start, finish))
    elif ft_id is None and start is not None and finish is not None:
        change = True
        temp: str = "WHERE andon.time > '{}' AND andon.time < '{}'"
        sql_date = sql_date.format(temp.format(start, finish))
    if change is False:
        sql_date = sql_date.format("")
    data = g.session.execute(text(sql_date)).fetchall()

    file_name = "{}.xlsx".format(local_time())
    if file is not None:
        file_name = "{}.xlsx".format(file)

    output = BytesIO()
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})
    worksheet_index = workbook.add_worksheet("设备历史报警数据表")
    worksheet_index.set_column("A:ZZ", 32)
    message: list = ["报警种类", "报警信息", "报警时间", "报警时间/秒", "操作用户", "设备位置", "设备名字"]
    worksheet_index.write_row(row=0, col=0, data=message)
    for index, itr in enumerate(data):
        sequence = index + 1
        worksheet_index.write_row(row=sequence, col=0, data=list(itr))

    # 在数据流之前关闭工作薄
    workbook.close()
    output.seek(0)
    resp = Response(output, content_type='application/octet-stream')
    resp.headers["Content-Disposition"] = "attachment; filename={0}; filename*=utf-8''{0}".format(quote(file_name))
    resp.headers.set("Access-Control-Expose-Headers", "*")
    return resp, 200


@api_routes.route("/andon/statistics", methods=["GET"])
@certification(auth=True, role=None)
def func_andon_statistics_get_api():
    """
    设备报警记录统计, 只要有token 都可以访问, API
    http://127.0.0.1:8080/api/andon/statistics
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "facility_id"   int            设备ID
    "start"         string         开始日期, 格式 %Y-%m-%d
    "finish"        string         结束日期, 格式 %Y-%m-%d
    "owner_id"      Int            默认(下拉，键值对)，可以不选, 排班名称，1白班， 2晚班
    }
    :return:
        数据流
    ---
    tags:
      - 设备报警
    """
    ft_id: Union[int, None] = request.args.get("facility_id", default=None, type=int)
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)
    owner_id: int = request.args.get("owner_id", default=0, type=int)

    if owner_id not in [0, 1, 2]:
        abort(code=400)

    if start is not None and len(start) == 0:
        start = None
    if finish is not None and len(finish) == 0:
        finish = None

    if (owner_id and start is None) or (owner_id and finish is None) or (owner_id and start != finish):
        return jsonify({'status': 4001, 'msg': "选择班次不能跨天查询或未指定日期"})

    back_start = start
    back_finish = finish

    if start is not None:
        start = datetime.strptime(start, "%Y-%m-%d").strftime("%Y-%m-%d %H:%M:%S")
    if finish is not None:
        temp_date_time = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    db_owner: Union[Owner, None] = g.session.query(Owner).filter(Owner.id == owner_id).first()
    if db_owner is not None and db_owner.start <= db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        finish = "{} {}".format(back_finish, db_owner.finish.strftime("%H:%M:%S"))
    elif db_owner is not None and db_owner.start > db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        hour = db_owner.finish.hour
        temp_date_time = datetime.strptime(back_finish, "%Y-%m-%d") + timedelta(days=1) + timedelta(hours=hour)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    sql_date = """
        SELECT
            facility.name, facility.workplace,
            facility.serials, andon_st.numbers, andon_st.totals
        FROM
        (
            SELECT
                facility_id, COUNT(facility_id) AS numbers, SUM(expenditure) AS totals
            FROM andon {} GROUP BY facility_id
        ) AS andon_st
        INNER JOIN facility ON andon_st.facility_id = facility.id ORDER BY facility.id
    """
    change = False
    if ft_id is not None and start is None and finish is None:
        change: bool = True
        sql_date = sql_date.format("WHERE facility_id = {}".format(ft_id))
    elif ft_id is not None and start is not None and finish is not None:
        change: bool = True
        temp: str = "WHERE facility_id = {} AND time > '{}' AND time < '{}'"
        sql_date = sql_date.format(temp.format(ft_id, start, finish))
    elif ft_id is None and start is not None and finish is not None:
        change: bool = True
        temp: str = "WHERE time > '{}' AND time < '{}'"
        sql_date = sql_date.format(temp.format(start, finish))
    if not change:
        sql_date = sql_date.format("")
    db_data = g.session.execute(text(sql_date)).fetchall()

    data: list = []
    parm: Union[str, None] = None
    db_fts: List[Facility] = g.session.query(Facility).filter(Facility.uuid4 != parm).order_by(Facility.id).all()
    for index0, itr0 in enumerate(db_fts):
        init_awaits_numbers: int = 0
        init_awaits_totals: int = 0
        for ft_nm, ft_wk, ft_s2, as_ns, as_ts in db_data:
            if ft_s2 == itr0.serials:
                init_awaits_numbers = as_ns
                init_awaits_totals = as_ts
                break
        if init_awaits_totals is None:
            init_awaits_totals = 0

        hour_format = int(init_awaits_totals / 3600)
        mount_format = int((init_awaits_totals - (3600 * hour_format)) / 60)
        se_format = int(init_awaits_totals - (3600 * hour_format) - (60 * mount_format))
        time_format = "{}时{}分{}秒".format(hour_format, mount_format, se_format)

        data.append({
            "facility_name": itr0.name,
            "facility_workplace": itr0.workplace,
            "facility_serials": itr0.serials,
            "awaits_numbers": init_awaits_numbers,
            "awaits_totals": init_awaits_totals,
            "time_format": time_format
        })
    docs: dict = {
        "facility_name": "设备名称",
        "facility_workplace": "设备位置",
        "facility_serials": "设备序列号",
        "awaits_numbers": "发生次数",
        "awaits_totals": "时间/单位秒",
        "time_format": "秒转时间格式"
    }
    return jsonify({'status': 200, 'data': data, "docs": docs})


@api_routes.route("/andon/statistics/output", methods=["GET"])
def func_andon_statistics_output_get_api():
    """
    设备报警记录统计导出EXCEL, 只要有token 都可以访问, API
    http://127.0.0.1:8080/api/andon/statistics/output
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "facility_id"   int            设备ID
    "start"         string         开始日期, 格式 %Y-%m-%d
    "finish"        string         结束日期, 格式 %Y-%m-%d
    "owner_id"      Int            默认(下拉，键值对)，可以不选, 排班名称，1白班， 2晚班
    }
    :return:
        数据流
    ---
    tags:
      - 设备报警
    """
    ft_id: Union[int, None] = request.args.get("facility_id", default=None, type=int)
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)
    owner_id: int = request.args.get("owner_id", default=0, type=int)

    if owner_id not in [0, 1, 2]:
        abort(code=400)

    if start is not None and len(start) == 0:
        start = None
    if finish is not None and len(finish) == 0:
        finish = None

    if (owner_id and start is None) or (owner_id and finish is None) or (owner_id and start != finish):
        return jsonify({'status': 4001, 'msg': "选择班次不能跨天查询或未指定日期"})

    back_start = start
    back_finish = finish

    if start is not None:
        start = datetime.strptime(start, "%Y-%m-%d").strftime("%Y-%m-%d %H:%M:%S")
    if finish is not None:
        temp_date_time = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    db_owner: Union[Owner, None] = g.session.query(Owner).filter(Owner.id == owner_id).first()
    if db_owner is not None and db_owner.start <= db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        finish = "{} {}".format(back_finish, db_owner.finish.strftime("%H:%M:%S"))
    elif db_owner is not None and db_owner.start > db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        hour = db_owner.finish.hour
        temp_date_time = datetime.strptime(back_finish, "%Y-%m-%d") + timedelta(days=1) + timedelta(hours=hour)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    sql_date = """
        SELECT
            facility.name, facility.workplace,
            facility.serials, andon_st.numbers, andon_st.totals
        FROM
        (
            SELECT
                facility_id, COUNT(facility_id) AS numbers, SUM(expenditure) AS totals
            FROM andon {} GROUP BY facility_id
        ) AS andon_st
        INNER JOIN facility ON andon_st.facility_id = facility.id ORDER BY facility.id
    """
    change = False
    if ft_id is not None and start is None and finish is None:
        change: bool = True
        sql_date = sql_date.format("WHERE facility_id = {}".format(ft_id))
    elif ft_id is not None and start is not None and finish is not None:
        change: bool = True
        temp: str = "WHERE facility_id = {} AND time > '{}' AND time < '{}'"
        sql_date = sql_date.format(temp.format(ft_id, start, finish))
    elif ft_id is None and start is not None and finish is not None:
        change: bool = True
        temp: str = "WHERE time > '{}' AND time < '{}'"
        sql_date = sql_date.format(temp.format(start, finish))
    if not change:
        sql_date = sql_date.format("")
    db_data = g.session.execute(text(sql_date)).fetchall()

    output = BytesIO()
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})
    worksheet_index = workbook.add_worksheet("设备历史报警统计表")
    worksheet_index.set_column("A:ZZ", 32)
    message: list = ["设备名字", "设备位置", "设备编号", "报警次数", "报警时间/秒", "报警时间格式化"]
    worksheet_index.write_row(row=0, col=0, data=message)

    parm: Union[str, None] = None
    db_fts: List[Facility] = g.session.query(Facility).filter(Facility.uuid4 != parm).order_by(Facility.id).all()
    for index0, itr0 in enumerate(db_fts):
        init_awaits_numbers: int = 0
        init_awaits_totals: int = 0
        for ft_nm, ft_wk, ft_s2, as_ns, as_ts in db_data:
            if ft_s2 == itr0.serials:
                init_awaits_numbers = as_ns
                init_awaits_totals = as_ts
                break
        if init_awaits_totals is None:
            init_awaits_totals = 0

        hour_format = int(init_awaits_totals / 3600)
        mount_format = int((init_awaits_totals - (3600 * hour_format)) / 60)
        se_format = int(init_awaits_totals - (3600 * hour_format) - (60 * mount_format))
        time_format = "{}时{}分{}秒".format(hour_format, mount_format, se_format)

        sequence = index0 + 1
        t_array = [itr0.name, itr0.workplace, itr0.serials, init_awaits_numbers, init_awaits_totals,  time_format]
        worksheet_index.write_row(row=sequence, col=0, data=t_array)

    file_name = "{}.xlsx".format(local_time())
    # 在数据流之前关闭工作薄
    workbook.close()
    output.seek(0)
    resp = Response(output, content_type='application/octet-stream')
    resp.headers["Content-Disposition"] = "attachment; filename={0}; filename*=utf-8''{0}".format(quote(file_name))
    resp.headers.set("Access-Control-Expose-Headers", "*")
    return resp, 200
